package com.symc.sims0213xfw.dao.impl;

import com.symc.sims0213xfw.bean.College;
import com.symc.sims0213xfw.dao.CollegeDao;
import com.symc.sims0213xfw.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @Author: 凤文  沈阳医学院2019级医学信息工程 0213
 * @CreateTime: 2022/01/26 13:28
 * @Description:
 */
public class CollegeDaoImpl implements CollegeDao {
    private static QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

    @Override
    public List<College> queryListVisibleCollege() {
        String sql = "SELECT id,NAME FROM college WHERE isVisible = 1";
        List<College> colleges = null;
        try {
            colleges = qr.query(sql,
                    new BeanListHandler<College>(College.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return colleges;
    }

    @Override
    public College queryVisibleCollege(Integer id) {
        String sql = "select id,NAME from college where isVisible=1 and id=?";
        College college = null;
        try {
            college = qr.query(sql, new BeanHandler<>(College.class), id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return college;
    }

    @Override
    public College queryCollege(Integer id) {
        String sql = "select id,NAME from college where id=?";
        College college = null;
        try {
            college = qr.query(sql, new BeanHandler<>(College.class), id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return college;
    }

    @Override
    public int addCollege(Integer id, String name) {
        String sql = "INSERT INTO college VALUES(?,?,1)";
        int i = 0;
        try {
            i = qr.update(sql, new Object[]{id, name});
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }

    @Override
    public int updateCollege(Integer id, String name) {
        String sql = "UPDATE college SET NAME=?,isVisible=1 WHERE id=?";
        int i = 0;
        try {
            i = qr.update(sql, new Object[]{name, id});
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }

    @Override
    public int deleteVisibleCollege(Integer id) {
        String sql = "UPDATE college SET isVisible=0 WHERE id=?";
        int i = 0;
        try {
            i = qr.update(sql, id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }
}
